package com.syni.mdd.yhd.common.all.dao.jpa;

import com.syni.mdd.yhd.common.all.dao.BaseJPADao;
import com.syni.mdd.yhd.common.all.entity.BmsDxGroupBuy;
import com.syni.mdd.yhd.common.all.utils.SpecificationUtils;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.Query;

import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Set;

public interface BmsDxGroupBuyDao extends BaseJPADao<BmsDxGroupBuy,Integer> {
    BmsDxGroupBuy findByIdAndIsDelete(Integer groupBuyId,Integer isDelete);

    @Query(value = "select bdgb.sell_inventory num from bms_dx_group_buy bdgb where bdgb.id = ?1",nativeQuery = true)
    Integer countGroupBuySellNum(Integer groupId);

    List<BmsDxGroupBuy> findByIdIn(Set ids);

    /**获取折扣最大的独家团购**/
    @Query(value = "select bdgb.* from bms_dx_group_buy bdgb,(" +
            "select business_id,min(discount_num) discount_num from bms_dx_group_buy where is_exclusive = 1 and status = 1 and is_delete = 0 group by business_id) bd " +
            "where bdgb.business_id = bd.business_id and bdgb.business_id in (?1) and bdgb.discount_num = bd.discount_num and bdgb.is_exclusive = 1 and bdgb.status = 1 and is_delete = 0 group by bdgb.business_id order by bdgb.sell_num desc",nativeQuery = true)
    List<BmsDxGroupBuy> findMostDiscountPreBuy(Set businessIds);

    /**获取折扣最大的独家团购**/
    @Query(value = "select bdgb.* from bms_dx_group_buy bdgb,(" +
            "select business_id,min(discount) discount from bms_dx_group_buy where is_exclusive = 1 and status = 1 and is_delete = 0 group by business_id) bd " +
            "where bdgb.business_id = bd.business_id and bdgb.discount = bd.discount and bdgb.is_exclusive = 1 and bdgb.status = 1 and is_delete = 0 group by bdgb.business_id order by bdgb.sell_num desc",nativeQuery = true)
    List<BmsDxGroupBuy> findMostDiscountPreBuy();

    /**
     * @Description 获取折扣最大的限时团购
     * @Author: cfx
     * @Date: 2019/11/14
     */
    @Query(value = "select bdgb.* from bms_dx_group_buy bdgb,(" +
            "select business_id,min(discount_num) discount_num from bms_dx_group_buy where is_time_limit = 1 and status = 1 and is_delete = 0 group by business_id) bd " +
            "where bdgb.business_id = bd.business_id and bd.business_id in (?1) and bdgb.discount_num = bd.discount_num and bdgb.is_time_limit = 1 and bdgb.status = 1 and is_delete = 0 group by bdgb.business_id order by bdgb.sell_num desc",nativeQuery = true)
    List<BmsDxGroupBuy> findMostDiscountLimitBuy(Set businessIds);

    /**获取折扣最大的普通团购**/
    @Query(value = "select bdgb.* from bms_dx_group_buy bdgb,(" +
            "select business_id,min(discount) discount_num from bms_dx_group_buy where is_exclusive = 0 and is_time_limit = 0 and status = 1 and is_delete = 0 group by business_id) bd " +
            "where bdgb.business_id = bd.business_id and bdgb.discount = bd.discount and bdgb.is_exclusive = 0 and bdgb.is_time_limit = 0 and bdgb.status = 1 and is_delete = 0 group by bdgb.business_id order by bdgb.sell_num desc",nativeQuery = true)
    List<BmsDxGroupBuy> findOrdinaryMoreGroupBuy();

    /**
     * @Description 获取所有上线团购（同商家的情况折扣最大）
     * @Author: cfx
     * @Date: 2019/12/11
     */
    @Query(value = "select bdgb.* from bms_dx_group_buy bdgb,(" +
            "select business_id,min(discount) discount_num from bms_dx_group_buy where status = 1 and is_delete = 0 group by business_id) bd " +
            "where bdgb.business_id = bd.business_id and bdgb.discount_num = bd.discount_num and bdgb.status = 1 and is_delete = 0 group by bdgb.business_id order by bdgb.sell_num desc",nativeQuery = true)
    List<BmsDxGroupBuy> findAllMoreGroupBuy();

    /**
     * @Description 获取所有上线团购（同商家的情况折扣最大）
     * @Param ids 特定的团购id集
     * @Author: cfx
     * @Date: 2019/12/11
     */
    @Query(value = "select bdgb.* from bms_dx_group_buy bdgb,(" +
            "select business_id,min(discount) discount_num from bms_dx_group_buy where status = 1 and is_delete = 0 and id in(?1) group by business_id) bd " +
            "where bdgb.business_id = bd.business_id and bdgb.discount_num = bd.discount_num and bdgb.status = 1 and bdgb.id in (?1) and is_delete = 0 group by bdgb.business_id order by bdgb.sell_num desc",nativeQuery = true)
    List<BmsDxGroupBuy> findAllMoreGroupBuyByRemoval(Set ids);

    /**
     * @Description 获取所有上线团购（普通去重）
     * @Param ids 特定的团购id集
     * @Author: cfx
     * @Date: 2019/12/11
     */
    @Query(value = "select bdgb.* from bms_dx_group_buy bdgb,(" +
            "select business_id,min(discount) discount_num from bms_dx_group_buy where status = 1 and is_delete = 0 and id in(?1) group by business_id) bd " +
            "where bdgb.business_id = bd.business_id and bdgb.discount_num = bd.discount_num and bdgb.status = 1 and bdgb.id in (?1) and is_delete = 0 group by bdgb.business_id order by bdgb.sell_num desc",nativeQuery = true)
    List<BmsDxGroupBuy> findAllMoreGroupBuyByGen(Set ids);

    /**获取折扣最大的普通团购**/
    @Query(value = "select bdgb.* from bms_dx_group_buy bdgb,(" +
            "select business_id,min(discount_num) discount_num from bms_dx_group_buy where is_exclusive = 0 and is_time_limit = 0 and status = 1 and is_delete = 0 group by business_id) bd " +
            "where bdgb.business_id = bd.business_id and bdgb.business_id in (?1) and bdgb.discount_num = bd.discount_num and bdgb.is_exclusive = 0 and bdgb.is_time_limit = 0 and bdgb.status = 1 and is_delete = 0 group by bdgb.business_id order by bdgb.sell_num desc",nativeQuery = true)
    List<BmsDxGroupBuy> findOrdinaryMoreGroupBuy(Set businessIds);

    /**获取折扣最大的普通团购（分页）**/
    @Query(value = "select bdgb.* from bms_dx_group_buy bdgb,(" +
            "select business_id,min(discount) discount from bms_dx_group_buy where is_exclusive = 0 and is_time_limit = 0 and status = 1 and is_delete = 0 group by business_id) bd " +
            "where bdgb.business_id = bd.business_id and bdgb.discount = bd.discount and bdgb.is_exclusive = 0 and bdgb.is_time_limit = 0 and bdgb.status = 1 and is_delete = 0 group by bdgb.business_id",nativeQuery = true)
    List<BmsDxGroupBuy> findOrdinaryMoreGroupBuy(Pageable pageable);

    /**获取折扣最大的普通团购（分页）**/
    @Query(value = "select bdgb.* from bms_dx_group_buy bdgb,(" +
            "select business_id,min(discount_num) discount_num from bms_dx_group_buy where is_exclusive = 0 and is_time_limit = 0 and status = 1 and is_delete = 0 group by business_id) bd " +
            "where bdgb.business_id = bd.business_id and bdgb.business_id in (?1) and bdgb.discount_num = bd.discount_num and bdgb.is_exclusive = 0 and bdgb.is_time_limit = 0 and bdgb.status = 1 and is_delete = 0 group by bdgb.business_id",nativeQuery = true)
    List<BmsDxGroupBuy> findOrdinaryMoreGroupBuy(Set businessIds,Pageable pageable);

    @Query(value = "select business_id businessId,count(*) num from " +
            "bms_dx_group_buy bdgb where status = 1 and business_id in(?1) group by business_id HAVING num > 1",nativeQuery = true)
    List<Map<String, Object>> countBusinessMoreGroupBuy(Set<Integer> businessIds);

    /**查询独家团购id**/
    @Query(value = "select bdgb.id from bms_dx_group_buy bdgb,(" +
            "select business_id,min(discount_num) discount_num from bms_dx_group_buy where is_exclusive = 1 and status = 1 and is_delete = 0 group by business_id) bd " +
            "where bdgb.business_id = bd.business_id and bdgb.discount_num = bd.discount_num and  bdgb.is_exclusive = 1 and bdgb.status = 1 and is_delete = 0 group by bdgb.business_id",nativeQuery = true)
    List<Map<String,Object>> findSpecialGroupBuyByEx();


    /**查询限时团购id(折扣最大)**/
    @Query(value = "select bdgb.id from bms_dx_group_buy bdgb,(" +
            "select business_id,min(discount_num) discount_num from bms_dx_group_buy where is_time_limit = 1 and status = 1 and is_delete = 0 group by business_id) bd " +
            "where bdgb.business_id = bd.business_id and bdgb.discount_num = bd.discount_num and  bdgb.is_time_limit = 1 and bdgb.status = 1 and is_delete = 0 group by bdgb.business_id",nativeQuery = true)
    List<Map<String,Object>> findSpecialGroupBuyByTimeLimit();


    @Query(value = "select id from bms_dx_group_buy bdgb where status = 1 and is_delete = 0 and group_name like %?1% and group_type in (0,1,2,3) group by bdgb.business_id",nativeQuery = true)
    List<Map<String, Object>> getGroupBuyIdsByGroupName(String searchText);

    @Query(value = "select bdgb.id from bms_dx_group_buy bdgb,bms_business bb where bdgb.business_id = bb.id and bdgb.status = 1 and bdgb.is_delete = 0 and " +
            "bb.vendor_name like %?1% ",nativeQuery = true)
    List<Map<String, Object>> getGroupBuyIdsByVendorName(String searchText);

    @Query(value = "select bdgb.* from bms_dx_group_buy bdgb," +
            "(select business_id,min(discount_num) discount_num from bms_dx_group_buy bd left join bms_business bb on bd.business_id = bb.id where bd.status = 1 " +
            "and bd.is_delete = 0 and bb.vendor_name like %?1% group by bd.business_id) bd LEFT JOIN bms_business bb1 on bd.business_id = bb1.id  " +
            " where bdgb.business_id = bd.business_id  and bdgb.discount_num = bd.discount_num  and bdgb.status = 1 and bdgb.is_delete = 0 and bb1.vendor_name like %?1% group by bdgb.business_id",nativeQuery = true)
    List<Map<String, Object>> getGroupBuyIdsByVendorNameAndDistinct(String searchText);


    @Query(value = "select bdgb.id from bms_dx_group_buy bdgb,bms_business bb where bdgb.business_id = bb.id and bdgb.status = 1 and bdgb.is_delete = 0 and " +
            "bb.vendor_name like %?1% group by bdgb.business_id",nativeQuery = true)
    List<Map<String, Object>> getGroupBuyIdByVendorName(String searchText);

    @Query(value = "select bdgb.id from bms_dx_group_buy bdgb,bms_business bb " +
            "where bdgb.business_id = bb.id and bdgb.status = 1 and bdgb.is_delete = 0 and bdgb.group_type in (0,1,2,3) and bb.id is not null and " +
            "((bb.vendor_name like %?1%) or (bdgb.group_name like %?1%))",nativeQuery = true)
    List<Map<String, Object>> getGroupBuyByGroupOrVendorName(String searchText);

    @Query(value = "select t.id from (select DISTINCT bb.id id from bms_dx_group_buy bdgb " +
            "left join bms_business bb on bdgb.business_id = bb.id " +
            "where bdgb.status = 1 and bdgb.is_delete = 0 and bb.addr_city = ?1 and bb.addr_district = ?2) t," +
            "bms_business_info bi where bi.alipay_status = 1 and t.id = bi.business_id ",nativeQuery = true)
    List<Map<String, Object>> findLocalGroupBuy(String addrCity, String addrDistrict);

    @Query(value = "select bb.id from bms_dx_group_buy bdgb left join bms_business bb on bdgb.business_id = bb.id " +
            "where bdgb.status = 1 and bdgb.is_delete = 0 and bb.addr_city = ?1 ",nativeQuery = true)
    List<Map<String, Object>> findLocalGroupBuy(String addrCity);

    List<BmsDxGroupBuy> findByStatusAndIsExclusive(Integer status,Integer isExclusive);

    List<BmsDxGroupBuy> findByStatusAndIsTimeLimit(Integer status,Integer isTimeLimit);

    List<BmsDxGroupBuy> findByStatusAndGroupType(Integer status,Integer groupType);

    List<BmsDxGroupBuy> findByStatusAndDiscountNumLessThan(Integer status,Double discountNum);

    @Query(value = "select * from (select bdgb.*,(POWER(MOD(ABS(bb.longitude - ?3),360),2) + POWER(ABS(bb.latitude - ?4),2)) AS distance " +
            "from bms_dx_group_buy bdgb " +
            "left join bms_business bb on bdgb.business_id = bb.id " +
            "where bdgb.status = 1 and bdgb.is_delete = 0 and bb.addr_city = ?1 and bb.addr_district = ?2 " +
            "and bdgb.is_exclusive = 1 order by distance asc) t," +
            "bms_business_info bi where bi.alipay_status = 1 and t.business_id = bi.business_id ",nativeQuery = true)
    List<BmsDxGroupBuy> findLocalPreGroupBuy(String addrCity, String addrDistrict,String lnt,String lat,Pageable pageable);

    @Query(value = "select count(t.id) from (select bdgb.id,bdgb.business_id " +
            "from bms_dx_group_buy bdgb " +
            "left join bms_business bb on bdgb.business_id = bb.id " +
            "where bdgb.status = 1 and bdgb.is_delete = 0 and bb.addr_city = ?1 and bb.addr_district = ?2 " +
            "and bdgb.is_exclusive = 1) t," +
            "bms_business_info bi where bi.alipay_status = 1 and t.business_id = bi.business_id ",nativeQuery = true)
    Integer countLocalPreGroupBuy(String addrCity, String addrDistrict);

    @Query(value = "select * from (select bdgb.*,(POWER(MOD(ABS(bb.longitude - ?3),360),2) + POWER(ABS(bb.latitude - ?4),2)) AS distance " +
            "from bms_dx_group_buy bdgb " +
            "left join bms_business bb on bdgb.business_id = bb.id " +
            "where bdgb.status = 1 and bdgb.is_delete = 0 and bb.addr_city = ?1 and bb.addr_district = ?2 " +
            "and bdgb.is_time_limit = 1 order by distance asc) t," +
            "bms_business_info bi where bi.alipay_status = 1 and t.business_id = bi.business_id ",nativeQuery = true)
    List<BmsDxGroupBuy> findLocalLimitGroupBuy(String addrCity, String addrDistrict,String lnt,String lat,Pageable pageable);

    @Query(value = "select count(t.id) from (select bdgb.id as id,bdgb.business_id " +
            "from bms_dx_group_buy bdgb " +
            "left join bms_business bb on bdgb.business_id = bb.id " +
            "where bdgb.status = 1 and bdgb.is_delete = 0 and bb.addr_city = ?1 and bb.addr_district = ?2 " +
            "and bdgb.is_time_limit = 1) t," +
            "bms_business_info bi where bi.alipay_status = 1 and t.business_id = bi.business_id ",nativeQuery = true)
    Integer countLocalLimitGroupBuy(String addrCity, String addrDistrict);

    @Query(value = "select bdgb.id from bms_dx_group_buy bdgb " +
            "LEFT JOIN bms_business bb on bdgb.business_id = bb.id " +
            "LEFT JOIN bms_business_label bbl on bb.label = bbl.label_name " +
            "where bdgb.status = 1 and bdgb.group_type in (?1) and bbl.id is not null and bbl.id in(?2) ",nativeQuery = true)
    Set<Integer> getGroupBuyByLabelIds(Set<Integer> groupTypes,List<Integer> labelIds);

    @Query(value = "select bdgb.id from bms_dx_group_buy bdgb " +
            "inner JOIN bms_business_type_relation bbtr on bdgb.business_id = bbtr.bms_business_id " +
            "where bdgb.status = 1 and bdgb.group_type in (?1) and bbtr.bms_type_id = ?2 ",nativeQuery = true)
    Set<Integer> getGroupBuyByTypeIds(Set<Integer> groupTypes, Integer typeId);

    @Query(value = "select bdgb.id from bms_dx_group_buy bdgb " +
            "INNER JOIN bms_business_area_relation bbar on bdgb.business_id = bbar.business_id " +
            "where bdgb.status = 1 and bdgb.group_type in (?1) and bbar.area_id IN (?2) ",nativeQuery = true)
    Set<Integer> getGroupBuyByCirIds(Set<Integer> groupTypes, Set<Integer> bcIds);

    @Query(value = "select bdgb.id from bms_dx_group_buy bdgb " +
            "INNER JOIN bms_business bb on bdgb.business_id = bb.id " +
            "INNER JOIN bms_business_info bbi on bbi.business_id = bdgb.business_id " +
            "where bdgb.status = 1 and bbi.alipay_status = 1 and bdgb.group_type in (?3) " +
            "and IF(?1 != '', bb.addr_city=?1,1=1) and IF(?2 != '',bb.addr_district=?2,1=1)",nativeQuery = true)
    Set<Integer> getAllGroupBuy(String addrCity, String addrDistrict, Set<Integer> groupTypes);

    @Query(value = "select count(bdgb.id) num ,business_id businessId from bms_dx_group_buy bdgb " +
            "where bdgb.business_id in (?1) and bdgb.group_type in (?2) and bdgb.status in (?3) group by business_id",nativeQuery = true)
    List<Map<String,Object>> countBusinessGroupBuyNum(Set businessIds,Set groupTypes,Set status);


    @Query(value = "select bdgb.id,(POWER(MOD(ABS(bb.longitude - ?1),360),2) + POWER(ABS(bb.latitude - ?2),2)) AS distance " +
            "from bms_dx_group_buy bdgb " +
            "INNER JOIN bms_business bb on bdgb.business_id = bb.id " +
            "INNER JOIN bms_business_info bbi on bbi.business_id = bdgb.business_id " +
            "where bdgb.status = 1 and bbi.alipay_status = 1 and bdgb.group_type in (?3)" +
            "order by distance asc limit 0,500",nativeQuery = true)
    Set<Integer> getGroupBuyByLatitudeLongitude(String longitude, String latitude, Set<Integer> groupTypes);

    @Query(value = "select bdgb.id,(POWER(MOD(ABS(bb.longitude - ?1),360),2) + POWER(ABS(bb.latitude - ?2),2)) AS distance " +
            "from bms_dx_group_buy bdgb " +
            "INNER JOIN bms_business bb on bdgb.business_id = bb.id " +
            "INNER JOIN bms_business_info bbi on bbi.business_id = bdgb.business_id " +
            "where bdgb.status = 1 and bbi.alipay_status = 1 and bdgb.group_type in (?3) and bdgb.id not in (?4)" +
            "order by distance asc limit 0,200",nativeQuery = true)
    Set<Integer> getGroupBuyByLatitudeLongitude(String longitude, String latitude,Set<Integer> groupTypes,Set<Integer> notInIds);

    @Query(value = "select count(distinct bdgb.business_id) " +
            "from bms_dx_group_buy bdgb " +
            "where bdgb.status = ?1 and bdgb.group_type in (?2) and bdgb.id in (?3) ",nativeQuery = true)
    Integer countGroupBuyNum(Integer status, Set groupTypes, Set ids);

    @Query(value = "select *,(POWER(MOD(ABS(bb.longitude - ?2),360),2) + POWER(ABS(bb.latitude - ?3),2)) AS distance " +
            "from bms_dx_group_buy bdgb left join bms_business bb on bdgb.business_id = bb.id " +
            "where bdgb.id in (?1) group by bdgb.business_id order by distance asc",nativeQuery = true)
    List<BmsDxGroupBuy> getNearGroupBuy(Set<Integer> resultIds,String longitude,String latitude,Pageable pageable);
}
